package org.jmc.refmanager.loader.util;

import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public final class JdbcUtil {
	public static final String ARG_DELIMITER = "delimiter";
	public static final String ARG_ENCLOSED_BY = "enclosedBy";
	public static final String ARG_QUERY = "query";
	public static final String ARG_SKIP_HEADER = "skipHeader";
	
	/**
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static List<String> listAllTables(Connection conn) throws SQLException {
		List<String> tables = new ArrayList<String>();
		
		DatabaseMetaData metadata = conn.getMetaData();
		String[] types = { "TABLE" };
		ResultSet rs = metadata.getTables(null, null, null, types);
		while (rs.next()) {
			String tableName = rs.getString("TABLE_NAME");
			tables.add(tableName);
		}
		
		return tables;
	}

	/**
	 * 
	 * @param conn
	 * @param tables
	 * @throws SQLException
	 */
	public static String listTable(Connection conn, String tableName) throws SQLException {
		StringBuffer buffer = new StringBuffer();
		String query = String.format("select * from %s a", tableName);
		PreparedStatement statement = conn.prepareStatement(query);
		int rowNo = 0;
		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			if (rowNo == 0) {
				buffer.append(printTableColumns(rs));
			}
			buffer.append(printResultRow(rs));
			rowNo++;
		}
		
		return buffer.toString();
	}

	/**
	 * 
	 * @param conn
	 * @param tableName
	 * @param out
	 * @param dumpProps
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void dumpTable(Connection conn, String tableName, Writer out, Properties dumpProps) throws SQLException, IOException {
		String query = String.format("select * from %s a", tableName);
		dumpProps.put(ARG_QUERY, query);
		dumpTable(conn, out, dumpProps);
	}

	/**
	 * 
	 * @param conn
	 * @param out
	 * @param
	 * @throws SQLException
	 * @throws IOException 
	 */
	public static void dumpTable(Connection conn, Writer out, Properties dumpProps) throws SQLException, IOException {
		String query = dumpProps.getProperty(ARG_QUERY);
		
		Util.checkRequired(ARG_QUERY, query);
		
		String delimiter = dumpProps.getProperty(ARG_DELIMITER, ";");
		String enclosedBy = dumpProps.getProperty(ARG_ENCLOSED_BY);
		boolean skipHeader = Boolean.valueOf(dumpProps.getProperty(ARG_SKIP_HEADER, "true"));
		
		PreparedStatement statement = null;
		ResultSet rs = null;
		
		try {
			statement = conn.prepareStatement(query);
			int rowNo = 0;
			rs = statement.executeQuery();
			while (rs.next()) {
				if (rowNo == 0 && !skipHeader) {
					dumpTableColumns(rs, out, delimiter, enclosedBy);
				}
				dumpResultRow(rs, out, delimiter, enclosedBy);
				rowNo++;
			}
		} finally {
			closeQuietly(statement, rs);
		}
	}

	/**
	 * 
	 * @param rs
	 * @param inSql if <code>true</code>, dump table as SQL format
	 * @return
	 * @throws SQLException
	 * @throws IOException 
	 */
	public static void dumpTableColumns(ResultSet rs, Writer out, String delimiter, String enclosedBy) throws SQLException, IOException {
		ResultSetMetaData metaData = rs.getMetaData();
		
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			int col = i + 1;
				if (i!=0) {
					out.write(delimiter);
				}
				if (Util.isEmptyOrNull(enclosedBy)) {
					out.write(metaData.getColumnName(col));
				} else {
					out.write(enclosedBy);
					out.write(metaData.getColumnName(col));
					out.write(enclosedBy);
				}
		}
		out.append("\n");
	}

	/**
	 * 
	 * @param rs
	 * @throws SQLException
	 */
	public static void dumpResultRow(ResultSet rs, Writer out, String delimiter, String enclosedBy) throws SQLException, IOException {
		ResultSetMetaData metaData = rs.getMetaData();
		
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			String column = metaData.getColumnName(i + 1);
			try {
				String value = rs.getString(column);
				// TODO BLOB pas pris en compte
				if (!Util.isEmptyOrNull(value)) {
					// in case of LOB
					value = value.replace("\n", "");
					value = value.replace("\r", "");
					value = value.replace("\t", "");
				}
				
				if (i!=0) {
					out.write(delimiter);
				}
				
				if (Util.isEmptyOrNull(enclosedBy)) {
					out.write(value);
				} else {
					out.write(enclosedBy);
					out.write(Util.getBlankIfNull(value));
					out.write(enclosedBy);
				}
				
//				if (value != null && !value.equals("null") && !value.equals("") && !value.equals("0")) {
//				}
			} catch (SQLException e) {
				throw new SQLException(column + ": " + e.getMessage(), e);
			}
		}
		out.write("\n");
	}

	/**
	 * 
	 * @param rs
	 * @param inSql if <code>true</code>, dump table as SQL format
	 * @return
	 * @throws SQLException
	 */
	public static String printTableColumns(ResultSet rs) throws SQLException {
		StringBuffer buffer = new StringBuffer(300);
		ResultSetMetaData metaData = rs.getMetaData();
		
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			int col = i + 1;
			buffer.append(metaData.getColumnName(col));
			buffer.append(" ");
			buffer.append(metaData.getColumnTypeName(col));
			buffer.append(" (");
			buffer.append(metaData.getPrecision(col));
			buffer.append(")");
		}
		
		buffer.append("\n");
		return buffer.toString();
	}
	
	/**
	 * 
	 * @param rs
	 * @throws SQLException
	 */
	public static String printResultRow(ResultSet rs) throws SQLException {
		StringBuffer buffer = new StringBuffer(300);
		ResultSetMetaData metaData = rs.getMetaData();
		
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			String column = metaData.getColumnName(i + 1);
			try {
				String value = rs.getString(column);
				// TODO BLOB pas pris en compte
				if (!Util.isEmptyOrNull(value)) {
					// in case of LOB
					value = value.replace("\n", "");
					value = value.replace("\r", "");
					value = value.replace("\t", "");
				}
				
				if (i!=0) {
					buffer.append(";");
				}
				buffer.append("\"");
				buffer.append(value);
				buffer.append("\"");
//				if (value != null && !value.equals("null") && !value.equals("") && !value.equals("0")) {
//				}
			} catch (SQLException e) {
				throw new SQLException(column + ": " + e.getMessage(), e);
			}
		}
		buffer.append("\n");
		return buffer.toString();
	}

	/**
	 * 
	 * @param conn
	 * @param queries
	 * @param queryName
	 * @throws SQLException
	 */
	public static int executeQuery(Connection conn, Properties queries, String queryName) throws SQLException {
		int updatesCount = 0;
		PreparedStatement stmt = null;
		try {
			stmt = getPreparedStatement(conn, queries, queryName, null);
			updatesCount = stmt.executeUpdate();
			conn.commit();
		} finally {
			JdbcUtil.closeQuietly(stmt);
		}
		
		return updatesCount;
	}

	/**
	 * 
	 * @param conn
	 * @param sqlQueries
	 * @param queryName
	 * @param staticArgs
	 * @return
	 * @throws SQLException
	 */
	public static PreparedStatement getPreparedStatement(Connection conn, Properties sqlQueries, String queryName, Object[] staticArgs) throws SQLException {
		String query = getQuery(sqlQueries, queryName, staticArgs);
		return conn.prepareStatement(query);
	}
	
	/**
	 * 
	 * @param sqlQueries
	 * @param queryName
	 * @param staticArgs
	 * @return
	 */
	public static String getQuery(Properties sqlQueries, String queryName, Object[] staticArgs) {
		if (sqlQueries == null) {
			throw new IllegalArgumentException("Le fichier des requetes est introuvable.");
		}
		
		String template = sqlQueries.getProperty(queryName);
		
		if (template == null) {
			throw new IllegalArgumentException("La requete " + queryName + " est introuvable.");
		}
		
		return format(template, staticArgs);
	}
	
	/**
	 * 
	 * @param template
	 * @param args
	 * @return
	 */
	public static String format(String template, Object[] args) {
		String cleanedTemplate = replaceSingleQuotes(template);
		MessageFormat mf = new MessageFormat(cleanedTemplate);
		String output = mf.format(args);
		return output;
	}

	/**
	 * 
	 * @param template
	 * @return
	 */
	private static String replaceSingleQuotes(String template) {
		String cleaned = template.replace("'", "''");
		return cleaned;
	}

	/**
	 * Closes the specified connection if it is not <code>null</code> and
	 * without throwing a SQL exception.
	 */
	public static void closeQuietly(Connection connection) {
		try {
			if (connection != null)
				connection.close();
		} catch (SQLException e) {
			Util.traceException(e);
		}
	}

	/**
	 * Closes the specified connection and statement if they are not
	 * <code>null</code> and without throwing a SQL exception.
	 */
	public static void closeQuietly(Connection connection, Statement statement) {
		try {
			if (statement != null)
				statement.close();
		} catch (SQLException e) {
			Util.traceException(e);
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				Util.traceException(e);
			}
		}
	}

	/**
	 * Closes the specified connection, statement and result set if they are not
	 * <code>null</code> and without throwing a SQL exception.
	 */
	public static void closeQuietly(Connection connection, Statement statement,
			ResultSet resultSet) {
		try {
			if (resultSet != null)
				resultSet.close();
		} catch (SQLException e) {
			Util.traceException(e);
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException e) {
				Util.traceException(e);
			} finally {
				try {
					if (connection != null)
						connection.close();
				} catch (SQLException e) {
					Util.traceException(e);
				}
			}
		}
	}

	/**
	 * Closes the specified result set if it is not <code>null</code> and
	 * without throwing a SQL exception.
	 */
	public static void closeQuietly(ResultSet resultSet) {
		try {
			if (resultSet != null)
				resultSet.close();
		} catch (SQLException e) {
			Util.traceException(e);
		}
	}

	/**
	 * Closes the specified statement if it is not <code>null</code> and without
	 * throwing a SQL exception.
	 */
	public static void closeQuietly(Statement statement) {
		try {
			if (statement != null)
				statement.close();
		} catch (SQLException e) {
			Util.traceException(e);
		}
	}

	/**
	 * Closes the specified statement and result set if they are not
	 * <code>null</code> and without throwing a SQL exception.
	 */
	public static void closeQuietly(Statement statement, ResultSet resultSet) {
		try {
			if (resultSet != null)
				resultSet.close();
		} catch (SQLException e) {
			Util.traceException(e);
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException e) {
				Util.traceException(e);
			}
		}
	}

	/**
	 * 
	 * @param dbMetaData
	 * @throws SQLException
	 */
	public static void printDatabaseInfo(DatabaseMetaData dbMetaData) throws SQLException {
		ResultSet databaseTypes = null;
		try {
			print("Information sur la base de donnees");
			String databaseProductName = dbMetaData.getDatabaseProductName();
			String databaseProductVersion = dbMetaData.getDatabaseProductVersion();
			print("------------------------------");
			print("Nom du produit : %s", databaseProductName);
			print("Version du produit: %s", databaseProductVersion);

			print("------------------------------");
			// gather max number of chars for the names of tables and columns
			int maximumColumnNameChars = dbMetaData.getMaxColumnNameLength();
			print("Nombre de char max pour le nom d'une colonne : %s", maximumColumnNameChars);

			int maximumTableNameChars = dbMetaData.getMaxTableNameLength();
			print("Nombre de char max pour le nom d'une table : %s", maximumTableNameChars);

			print("------------------------------");
			// report the available database types for the database being used
			databaseTypes = dbMetaData.getTypeInfo();
			print("Types supportes :");
			while (databaseTypes.next()) {
				String typeName = databaseTypes.getString("TYPE_NAME");
				print(typeName);
			}

			print("------------------------------");
			print("Support les mises a jour par batch : %s", dbMetaData.supportsBatchUpdates());
		} finally {
			closeQuietly(databaseTypes);
		}
	}

	private static void print(String message) {
		print(message, (Object[]) null);
	}

	private static void print(String message, Object... args) {
		System.out.printf(message, args);
		System.out.println();
	}
}
